package com.databasedictionary.utils;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * JDBC工具类
 * Created by sanhu on 2017/1/10.
 */
public class JdbcUtil {

	// 定义数据库的链接
	private Connection conn;
	// 定义sql语句的执行对象
	private PreparedStatement statement;
	// 定义查询返回的结果集合
	private ResultSet rs;
	private String username;

	// 初始化
	public JdbcUtil(String driver, String url, String username, String password) {
		try {
			Class.forName(driver);
			this.username = username;
			conn = DriverManager.getConnection(url, username, password);
			System.out.println("数据库连接成功");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 更新数据
	public boolean updateByParams(String sql, List params) throws SQLException {
		// 影响行数
		int result = -1;
		statement = conn.prepareStatement(sql);
		int index = 1;
		// 填充sql语句中的占位符
		if (null != params && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i ++) {
				statement.setObject(index ++, params.get(i));
			}
		}
		result = statement.executeUpdate();
		return result > 0 ? true : false;
	}

	// 查询多条记录
	public List<Map> selectByParams(String sql, List params) throws SQLException {
		List<Map> list = new ArrayList ();
		int index = 1;
		statement = conn.prepareStatement(sql);
		if (null != params && !params.isEmpty()) {
			for (int i = 0; i < params.size(); i ++) {
				statement.setObject(index++, params.get(i));
			}
		}
		rs = statement.executeQuery();
		ResultSetMetaData metaData = rs.getMetaData();
		int colsLen = metaData.getColumnCount();
		while (rs.next()) {
			Map map = new HashMap();
			for (int i = 0; i < colsLen; i ++) {
				String colsName = metaData.getColumnName(i + 1);
				Object colsValue = rs.getObject(colsName);
				if (null == colsValue) {
					colsValue = "";
				}
				map.put(colsName, colsValue);
			}
			list.add(map);
		}
		return list;
	}




	/**
	 * 获得表所有属性
	 * @param tableName
	 * @return
	 * @throws SQLException
	 */
	public List<Map<String, Object>> getTable(String tableName) throws SQLException {
		DatabaseMetaData dbMetaData = conn.getMetaData();


		ResultSet rs = dbMetaData.getPrimaryKeys(null, null, tableName);
		String pkName = "";
		while (rs.next()){
			pkName = rs.getString("COLUMN_NAME"); //主键名称
		}


		ResultSet rsColumns = dbMetaData.getColumns(conn.getCatalog(), username, tableName, null);
		List<Map<String, Object>> listMap = new ArrayList<Map<String, Object>>();
		int i = 0;

		while(rsColumns.next()){
			Map<String, Object> map = new HashMap<String, Object>();

			String tableCat = rsColumns.getString("TABLE_CAT");//表目录（可能为空）
			String tableSchemaName = rsColumns.getString("TABLE_SCHEM");//表的架构（可能为空）
			tableName = rsColumns.getString("TABLE_NAME");//表名
			String columnName = rsColumns.getString("COLUMN_NAME").toLowerCase();//列名
			int dataType = rsColumns.getInt("DATA_TYPE"); //对应的java.sql.Types类型
			String dataTypeName = rsColumns.getString("TYPE_NAME");//java.sql.Types类型名称
			int columnSize = rsColumns.getInt("COLUMN_SIZE");//列大小
			int decimalDigits = rsColumns.getInt("DECIMAL_DIGITS");//小数位数
			int numPrecRadix = rsColumns.getInt("NUM_PREC_RADIX");//基数（通常是10或2）
			int nullAble = rsColumns.getInt("NULLABLE");//是否允许为null
			String remarks = rsColumns.getString("REMARKS");//列描述
			String columnDef = rsColumns.getString("COLUMN_DEF");//默认值
			int sqlDataType = rsColumns.getInt("SQL_DATA_TYPE");//sql数据类型
			int sqlDatetimeSub = rsColumns.getInt("SQL_DATETIME_SUB");   //SQL日期时间分?
			int charOctetLength = rsColumns.getInt("CHAR_OCTET_LENGTH");   //char类型的列中的最大字节数
			int ordinalPosition = rsColumns.getInt("ORDINAL_POSITION");  //表中列的索引（从1开始）
			String isNullAble = rsColumns.getString("IS_NULLABLE");// ISO规则用来确定某一列的为空性。  是---如果该参数可以包括空值; 无---如果参数不能包含空值 ; 空字符串---如果参数为空性是未知的
			String isAutoincrement = rsColumns.getString("IS_AUTOINCREMENT");// 指示此列是否是自动递增 是---如果该列是自动递增;无---如果不是自动递增列;空字串---如果不能确定它是否;列是自动递增的参数是未知

			map.put("tableDBName", tableName);
			String[] tableNames = tableName.split("_");
			for (int j = 0; j < tableNames.length; j++) {
				if (j == 0) {
					tableName = tableNames[j];
				} else {
					tableName += tableNames[j].substring(0, 1).toUpperCase() + tableNames[j].substring(1);
				}
			}
			map.put("tableName", tableName);
			// 获得所有列的数目及实际列数
			ResultSetMetaData rsDatas = rsColumns.getMetaData();
			map.put("columnCount", rsDatas.getColumnCount());
			// 获得指定列的列名
			map.put("columnDBName", columnName);// DB列名
//			String[] columnLabels = columnName.split("_");
//			for (int j = 0; j < columnLabels.length; j++) {
//				if (j == 0) {
//					columnName = columnLabels[j].toLowerCase();
//				} else {
//					columnName += columnLabels[j].toLowerCase().substring(0, 1).toUpperCase()
//							+ columnLabels[j].toLowerCase().substring(1);
//				}
//			}
			map.put("columnName", columnName.toUpperCase());// 列名
			map.put("pkName", pkName);// 列名
			map.put("columnNameUpper", columnName.toUpperCase().substring(0, 1).toUpperCase() + columnName.substring(1));
			map.put("colunmIndex", i);
			// 获得指定列的数据类型
			map.put("columnType", String.valueOf(rsColumns.getObject("DATA_TYPE")));
			// 获得指定列的数据类型名
			switch(dataTypeName){
				case "INT":
					map.put("columnTypeName", "INTEGER");
					break;
				case "DATETIME":
					map.put("columnTypeName", "TIMESTAMP");
					break;
				case "TEXT":
					map.put("columnTypeName", "VARCHAR2");
					break;
				default:
					map.put("columnTypeName", String.valueOf(rsColumns.getObject("TYPE_NAME")));
					break;
			}

			// 对应数据类型的类
			map.put("dataType", dataType);
			map.put("dataTypeName", dataTypeName);
			String columnClassName = getClassNameForJavaType(dataType);
			map.put("columnClassFullName", columnClassName);
			map.put("columnClassName", columnClassName.substring(columnClassName.lastIndexOf(".") + 1));
			// 在数据库中类型的最大字符个数
			map.put("columnDisplaySize", charOctetLength);
			// 默认的列的标题
			map.put("columnLabel", remarks);
			// 在数据库中类型的最大字符个数
			map.put("columnDisplaySize", charOctetLength);
			//是否为空
			map.put("isNullAble", isNullAble);
			// 是否自动递增
			map.put("isAutoInctement", isAutoincrement);
			i++;

			listMap.add(map);
		}
		return listMap;
	}
	private static String getClassNameForJavaType(int javaType) {
		switch (javaType) {
			case -7:
			case 16:
				return "java.lang.Boolean";
			case -6:
			case 5:
			case 4:
				return "java.lang.Integer";
			case -5:
				return "java.math.BigInteger";
			case 3:
				return "java.math.BigDecimal";
			case 7:
				return "java.lang.Float";
			case 6:
			case 8:
			case 22:
				return "java.lang.Double";
			case -1:
			case 1:
			case 12:
			case -4:
			case -3:
			case -2:
				return "java.lang.String";
			case 91:
			case 93:
				return "java.sql.Date";
			case 92:
				return "java.sql.Time";
			default:
//		case 93:
//			return "java.sql.Timestamp";
		}

		return "java.lang.Object";
	}
	// 释放连接
	public void release() {
		try {
			if (null != rs) {
                rs.close();
            }
			if (null != statement) {
                statement.close();
            }
			if (null != conn) {
                conn.close();
            }
		} catch (SQLException e) {
			e.printStackTrace();
		}
		System.out.println("释放数据库连接");
	}

}
